package com.jason.SnippetManager.SQLiteCalls;

import java.sql.*;
import java.util.ArrayList;

public class DBHelper extends SQLStatics{

	private Connection conn;
	private Statement stat;
	private PreparedStatement prepstat;

	/**
	 * A simple database helper.\n 
	 * This constructor opens the connections 
	 */
	public DBHelper(){
		try {
			Class.forName( getForNameString());
			this.conn = DriverManager.getConnection( getConnString());
			this.stat = this.conn.createStatement();
			this.stat.executeUpdate("create table if not exists " +  getTableName() +" ("+ getColumnNames.TITLE.toString()+", "+
					 getColumnNames.BODY.toString()+", "+ getColumnNames.TAGS.toString()+");");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Creates the table, if it does not exist
	 * 
	 * @throws SQLException
	 */
	public void createDB() throws SQLException{
		this.stat.executeUpdate("create table if not exists " +  getTableName() +" ("+ getColumnNames.TITLE.toString()+", "+
				 getColumnNames.BODY.toString()+", "+ getColumnNames.TAGS.toString()+");");
	}

	/**
	 * Drops the table, if it exists
	 * 
	 * @throws SQLException
	 */
	public void dropDB() throws SQLException{
		this.stat.executeUpdate("drop table if exists " +  getTableName() +";");
	}

	/**
	 * Gets the titles
	 * 
	 * @return String [] title_list
	 * @throws SQLException
	 */
	public String[] getTitles() throws SQLException{
		ResultSet rs = this.stat.executeQuery("select "+getColumnNames.TITLE.toString()+
				" from " +  getTableName()+";");
		ArrayList<String> title_array_list  = new ArrayList<String>();
		while (rs.next()){
			title_array_list.add(rs.getString( getColumnNames.TITLE.toString() ));
		}
		rs.close();
		String [] title_list = new String[title_array_list.size()]; 
		title_array_list.toArray(title_list);
		return title_list;
	}
	
	public String[] getTitlesFromTagSearch(String searchTerm) throws SQLException{
		String sqlStatement = "SELECT "+getColumnNames.TITLE.toString()+
				" FROM "+getTableName()+" WHERE "+getColumnNames.TAGS.toString()+
				" LIKE ?"; 
		prepstat = conn.prepareStatement(sqlStatement);
		prepstat.setString(1, "%"+searchTerm+"%");
		ResultSet rs = prepstat.executeQuery();
		ArrayList<String> title_array_list  = new ArrayList<String>();
		while (rs.next()){
			title_array_list.add(rs.getString( getColumnNames.TITLE.toString() ));
		}
		rs.close();
		String [] title_list = new String[title_array_list.size()]; 
		title_array_list.toArray(title_list);
		return title_list;
	}


	/**
	 * Gets the body and tags based upon the given title
	 * 
	 * @param title
	 * @return
	 * @throws SQLException
	 */
	public String[] getBodyAndTags(String title) throws SQLException{
		String sqlStatement = "SELECT "+ getColumnNames.BODY.toString() +", "+ getColumnNames.TAGS.toString() +
				" from " +  getTableName()+" WHERE "+ getColumnNames.TITLE.toString()+" = ?";
		prepstat = conn.prepareStatement(sqlStatement);
		prepstat.setString(1, title);
		ResultSet rs = prepstat.executeQuery();
		ArrayList<String> title_array_list  = new ArrayList<String>();
		while (rs.next()){
			title_array_list.add(rs.getString( getColumnNames.BODY.toString()));
			title_array_list.add(rs.getString( getColumnNames.TAGS.toString()));
		}
		rs.close();
		String [] title_list = new String[title_array_list.size()]; 
		title_array_list.toArray(title_list);
		return title_list;
	}

	/**
	 * Updates an entry based upon its old title
	 * 
	 * @param oldtitle
	 * @param newtitle
	 * @param body
	 * @param tags
	 * @throws SQLException
	 */
	public void updateEntry (String oldtitle, String newtitle, String body, String tags) throws SQLException{
		String sqlStatement = "UPDATE "+ getTableName()+" " +
				"SET "+ getColumnNames.TITLE.toString()+"=?, " +
				 getColumnNames.BODY.toString()+"=?, " +
				 getColumnNames.TAGS.toString()+"=? " +
				"WHERE "+ getColumnNames.TITLE.toString()+"=?";
		prepstat = conn.prepareStatement(sqlStatement);
		prepstat.setString(1, newtitle);
		prepstat.setString(2, body);
		prepstat.setString(3, tags);
		prepstat.setString(4, oldtitle);
		prepstat.executeUpdate();
	}

	/**
	 * Creates a brand new entry
	 * 
	 * @param title
	 * @param body
	 * @param tags
	 * @throws SQLException
	 */
	public void newEntry (String title, String body, String tags) throws SQLException{
		prepstat = conn.prepareStatement("INSERT INTO "+ getTableName()+" VALUES (?,?,?)");
		prepstat.setString(1, title);
		prepstat.setString(2, body);
		prepstat.setString(3, tags);
		prepstat.executeUpdate();
	}

	/**
	 * Deletes and entry based upon the title
	 * 
	 * @param title
	 * @throws SQLException
	 */
	public void deleteEntry(String title) throws SQLException{
		prepstat = conn.prepareStatement("DELETE FROM "+ getTableName()+" " +
				"WHERE "+ getColumnNames.TITLE.toString()+"=?");
		prepstat.setString(1, title);
		prepstat.executeUpdate();
	}

	/**
	 * Closes the SQL connection
	 * 
	 * @throws SQLException
	 */
	public void closeConnection() throws SQLException{
		this.conn.close();
	}
}

